#encoding: utf-8

=begin
    用于寻找和修正疑似订单拆分丢失子订单的问题
=end

load 'rds_api.rb'
load 'split-orders-lib.rb'

#查找在order中对应的cart_id与在order_cart_info对应的cart_id实际数量不符的订单号
def find_leaked_orders conditions
    o2c = {}
    ids = []
    sql = "select cart_id, id from crmeb.eb_store_order #{ conditions if conditions!='' };"
    queryRds(sql).each do |q|
        cart_ids = JSON.parse q['cart_id']
        oid = q['id']
        o2c.store oid, cart_ids
        ids += [oid]
    end

    leaked_oids = []
    leaked_carts = []
    ids.each do |oid|
        sql = "select cart_id from crmeb.eb_store_order_cart_info where oid = #{oid}"
        cart_ids = []
        queryRds(sql).each do |q|
            cart_ids += [q['cart_id']]
        end
        if cart_ids.size != o2c[oid].size
            leaked_cart_ids = cart_ids - o2c[oid]
            puts "##{oid}"
            puts "      cart:   #{cart_ids.to_s}"
            puts "      order:  #{o2c[oid].to_s}"
            puts "      leaked: #{leaked_cart_ids.to_s}"
            leaked_oids += [oid]
        end
    end
    puts leaked_oids.to_s
end

#21-04-12 22:40
#8336之前都是重复cart_id的

def mark_qos_splitable qos
  qos.each do |oid|
      sql = "update crmeb.eb_store_order set order_code = '' where id = #{oid}"
      queryRds sql
  end
end

def check_qos qos
  qos.each do |oid|
    sql = "select order_code from crmeb.eb_store_order where id = #{oid}"
    puts sql
    queryRds(sql).each do |q|
      ap q
      puts "#{oid} #{q['order_code']}"
    end
  end
end


def report_fixed oids

    target_oids = []

    oids_str = oids.join(',')
    sql = "select order_code from crmeb.eb_store_order where id in (#{oids_str})"
    ocs = []
    queryRds(sql).each do |q|
       order_code = (q['order_code'].split('-'))[0]
       ocs += [order_code]
    end

    headers = ['id','order_code','order_id', 'real_name','user_phone','user_address','paid_time','express','mark','remark' ]
    names = ['id','order_code','order_id', 'real_name','user_phone','user_address','paid_time','express','mark','remark' ]
    items = []
    ocs.each do |oc|
        sql = "select id, order_code, order_id, real_name, user_phone, user_address, from_unixtime(pay_time) as paid_time, delivery_id as express, mark, remark from crmeb.eb_store_order where order_code like '#{oc}%'"
        queryRds(sql).each do |q|
            item = {
                "id" => q['id'],
                "order_code" => q['order_code'],
                "order_id" => q['order_id'],
                "real_name" => q['real_name'],
                "user_phone" => q['user_phone'],
                "user_address" => q['user_address'],
                "paid_time" => q['paid_time'],
                "express" => q['express'].to_s,
                "mark" => q['mark'],
                "remark" => q['remark']
             }
            items += [item]
            target_oids += [q['id']]
        end
    end
    save_to_excel headers, names, items, 'fixed_orders.xls'
    return target_oids
end

=begin #1

find_leaked_orders 'where paid =1'

#qos = [4850, 5069, 5070, 5071, 5072, 5073, 6615, 8336, 8343, 8350, 8353, 8354, 8358, 8370, 8380, 8382, 8383, 8388, 8392, 8410, 8417, 8427, 8428, 8430, 8440, 8441, 8445, 8449, 8453, 8456, 8466, 8470, 8472]
qos = [8336, 8343, 8350, 8353, 8354, 8358, 8370, 8380, 8382, 8383, 8388, 8392, 8410, 8417, 8427, 8428, 8430, 8440, 8441, 8445, 8449, 8453, 8456, 8466, 8470, 8472]
#在naviCat中保存了以上订单的原始数据

mark_qos_splitable qos

check_qos qos

=end

=begin #2
DONE
    find_leaked_orders 'where paid=1'
    qos = [4850, 5069, 5070, 5071, 5072, 5073, 6615, 8343, 8350, 8370, 8380, 8382, 8383, 8388, 8440]
    qos = [8343, 8350, 8370, 8380, 8382, 8383, 8388, 8440]
    find_leaked_orders 'where id in (8343)'
    check_qos qos

=end

=begin
DONE
#debug example 8343
orders = get_orders_ "where id in (8343,8493)"
register_books orders
archive_orders_ "where oid in (8343,8493)"
#splitted_orders = split_orders orders
=end

=begin
DONE
orders = get_orders_ "where id in (8350, 8370, 8380, 8382, 8383, 8388, 8440)"
splitted_orders = split_orders orders
=end

=begin
DONE 剩下的应该都是cart_id重复的老单
find_leaked_orders 'where paid = 1'
qos = [4850, 5069, 5070, 5071, 5072, 5073, 6615] 
=end

=begin
DONE
qos = [8336, 8343, 8350, 8353, 8354, 8358, 8370, 8380, 8382, 8383, 8388, 8392, 8410, 8417, 8427, 8428, 8430, 8440, 8441, 8445, 8449, 8453, 8456, 8466, 8470, 8472]
report_fixed qos
=end

=begin
重新生成所有问题订单及派生订单的分拣单
DONE

qos = [8336, 8343, 8350, 8353, 8354, 8358, 8370, 8380, 8382, 8383, 8388, 8392, 8410, 8417, 8427, 8428, 8430, 8440, 8441, 8445, 8449, 8453, 8456, 8466, 8470, 8472]
qos_list = '8336, 8343, 8350, 8353, 8354, 8358, 8370, 8380, 8382, 8383, 8388, 8392, 8410, 8417, 8427, 8428, 8430, 8440, 8441, 8445, 8449, 8453, 8456, 8466, 8470, 8472'
oids = report_fixed qos
oids_list = oids.join(',')

orders = get_orders_ "where id in (#{oids_list})"
register_books orders
archive_orders_ "where oid in (#{oids_list})"

=end
